From 63e36ee3847b5f3117fa6fdc810b81dcb8c6670f Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 20 Jul 2026 23:39:54 +0000 Subject: [PATCH] Create, document, index, and trigger SIV_STATUS_BOUT --- db/schemas/housekeeping/indexes/Makefile | 1 + .../indexes/create/siv_status_bout.m4 | 41 ++++ .../indexes/drop/siv_status_bout.m4 | 32 +++ db/schemas/housekeeping/tables/Makefile | 1 + .../tables/create/siv_status_bout.m4 | 41 ++++ db/schemas/lib/triggers/Makefile | 3 +- .../lib/triggers/create/siv_status_bout.m4 | 49 ++++ .../lib/triggers/drop/siv_status_bout.m4 | 23 ++ doc/src/epilog.inc.m4 | 24 ++ doc/src/housekeeping.m4 | 1 + doc/src/housekeeping/siv_status_bout.m4 | 215 ++++++++++++++++++ 11 files changed, 430 insertions(+), 1 deletion(-) create mode 100644 db/schemas/housekeeping/indexes/create/siv_status_bout.m4 create mode 100644 db/schemas/housekeeping/indexes/drop/siv_status_bout.m4 create mode 100644 db/schemas/housekeeping/tables/create/siv_status_bout.m4 create mode 100644 db/schemas/lib/triggers/create/siv_status_bout.m4 create mode 100644 db/schemas/lib/triggers/drop/siv_status_bout.m4 create mode 100644 doc/src/housekeeping/siv_status_bout.m4 diff --git a/db/schemas/housekeeping/indexes/Makefile b/db/schemas/housekeeping/indexes/Makefile index 9d1b2f1..d23e913 100644 --- a/db/schemas/housekeeping/indexes/Makefile +++ b/db/schemas/housekeeping/indexes/Makefile @@ -25,6 +25,7 @@ ORDER := aggression_event_log \ community_membership_update_log \ food_variations \ pantgrunt_event_log \ + siv_status_bout \ subadult_arrivals_log \ tlk_brecord_notes_codes diff --git a/db/schemas/housekeeping/indexes/create/siv_status_bout.m4 b/db/schemas/housekeeping/indexes/create/siv_status_bout.m4 new file mode 100644 index 0000000..d7f82a2 --- /dev/null +++ b/db/schemas/housekeeping/indexes/create/siv_status_bout.m4 @@ -0,0 +1,41 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +CREATE INDEX IF NOT EXISTS siv_status_bout_animid ON siv_status_bout + (animid); +CREATE INDEX IF NOT EXISTS siv_status_bout_startdate ON siv_status_bout + (startdate); +CREATE INDEX IF NOT EXISTS siv_status_bout_enddate ON siv_status_bout + (enddate); +CREATE INDEX IF NOT EXISTS siv_status_bout_sivstatus ON siv_status_bout + (sivstatus); +CREATE INDEX IF NOT EXISTS siv_status_bout_firsttestdate ON siv_status_bout + (firsttestdate); +CREATE INDEX IF NOT EXISTS siv_status_bout_lasttestdate ON siv_status_bout + (lasttestdate); +CREATE INDEX IF NOT EXISTS siv_status_bout_boutnumber ON siv_status_bout + (boutnumber); +CREATE INDEX IF NOT EXISTS siv_status_bout_namesused ON siv_status_bout + (namesused); +CREATE INDEX IF NOT EXISTS siv_status_bout_inbiotable ON siv_status_bout + (inbiotable); diff --git a/db/schemas/housekeeping/indexes/drop/siv_status_bout.m4 b/db/schemas/housekeeping/indexes/drop/siv_status_bout.m4 new file mode 100644 index 0000000..caf7903 --- /dev/null +++ b/db/schemas/housekeeping/indexes/drop/siv_status_bout.m4 @@ -0,0 +1,32 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +DROP INDEX IF EXISTS siv_status_bout_animid; +DROP INDEX IF EXISTS siv_status_bout_startdate; +DROP INDEX IF EXISTS siv_status_bout_enddate; +DROP INDEX IF EXISTS siv_status_bout_sivstatus; +DROP INDEX IF EXISTS siv_status_bout_firsttestdate; +DROP INDEX IF EXISTS siv_status_bout_lasttestdate; +DROP INDEX IF EXISTS siv_status_bout_boutnumber; +DROP INDEX IF EXISTS siv_status_bout_namesused; +DROP INDEX IF EXISTS siv_status_bout_inbiotable; diff --git a/db/schemas/housekeeping/tables/Makefile b/db/schemas/housekeeping/tables/Makefile index 0fc24d6..fc76f2c 100644 --- a/db/schemas/housekeeping/tables/Makefile +++ b/db/schemas/housekeeping/tables/Makefile @@ -26,6 +26,7 @@ ORDER := aggression_event_log \ community_membership_update_log \ food_variations \ pantgrunt_event_log \ + siv_status_bout \ subadult_arrivals_log \ tlk_brecord_notes_codes diff --git a/db/schemas/housekeeping/tables/create/siv_status_bout.m4 b/db/schemas/housekeeping/tables/create/siv_status_bout.m4 new file mode 100644 index 0000000..fbad2d3 --- /dev/null +++ b/db/schemas/housekeeping/tables/create/siv_status_bout.m4 @@ -0,0 +1,41 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`tablemacros.m4')dnl +include(`grants.m4')dnl +dnl + +CREATE TABLE siv_status_bout ( + key_column(`SIV_STATUS_BOUT', `ID', INTEGER) + ,animid TEXT NOT NULL + ,startdate DATE NOT NULL + ,enddate DATE NOT NULL + ,sivstatus TEXT NOT NULL + ,firsttestdate DATE NOT NULL + ,lasttestdate DATE NOT NULL + ,boutnumber INTEGER NOT NULL + ,namesused TEXT NOT NULL + ,inbiotable BOOLEAN NOT NULL + ,notes TEXT NOT NULL + notonlyspaces_check(`Notes') +); + +grant_priv(`SIV_STATUS_BOUT') diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index 932808a..2ef5b2e 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -62,7 +62,8 @@ ORDER := comm_ids \ community_membership_update_log \ food_variations \ pantgrunt_event_log \ - subadult_arrivals_log + subadult_arrivals_log \ + siv_status_bout DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/siv_status_bout.m4 b/db/schemas/lib/triggers/create/siv_status_bout.m4 new file mode 100644 index 0000000..0ed49a7 --- /dev/null +++ b/db/schemas/lib/triggers/create/siv_status_bout.m4 @@ -0,0 +1,49 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify it +dnl under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Triggers for the siv_status_bout table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'siv_status_bout_update_func'; +CREATE OR REPLACE FUNCTION siv_status_bout_update_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for siv_status_bout update trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + cannot_change(`SIV_STATUS_BOUT', `ID') + + RETURN NULL; + END; +$$; + + +RAISE INFO 'siv_status_bout_update_trigger'; +CREATE TRIGGER siv_status_bout_update_trigger + AFTER INSERT OR UPDATE + ON siv_status_bout FOR EACH ROW + EXECUTE PROCEDURE siv_status_bout_update_func(); diff --git a/db/schemas/lib/triggers/drop/siv_status_bout.m4 b/db/schemas/lib/triggers/drop/siv_status_bout.m4 new file mode 100644 index 0000000..726cde3 --- /dev/null +++ b/db/schemas/lib/triggers/drop/siv_status_bout.m4 @@ -0,0 +1,23 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Drop triggers for siv_status_bout table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS siv_status_bout_update_func() CASCADE; diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 2a747a1..62f641e 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -800,6 +800,30 @@ elo_ranks_daily_rst(sdb_male, MT)dnl .. |SIGHTINGS.Source| replace:: :ref:`Source ` +.. |SIV_STATUS_BOUT| replace:: :ref:`SIV_STATUS_BOUT ` +.. |SIV_STATUS_BOUT.ID| replace:: + :ref:`ID ` +.. |SIV_STATUS_BOUT.AnimID| replace:: + :ref:`AnimID ` +.. |SIV_STATUS_BOUT.StartDate| replace:: + :ref:`StartDate ` +.. |SIV_STATUS_BOUT.EndDate| replace:: + :ref:`EndDate ` +.. |SIV_STATUS_BOUT.SIVStatus| replace:: + :ref:`SIVStatus ` +.. |SIV_STATUS_BOUT.FirstTestDate| replace:: + :ref:`FirstTestDate ` +.. |SIV_STATUS_BOUT.LastTestDate| replace:: + :ref:`LastTestDate ` +.. |SIV_STATUS_BOUT.BoutNumber| replace:: + :ref:`BoutNumer ` +.. |SIV_STATUS_BOUT.NamesUsed| replace:: + :ref:`NamesUsed ` +.. |SIV_STATUS_BOUT.InBioTable| replace:: + :ref:`InBioTable ` +.. |SIV_STATUS_BOUT.Notes| replace:: + :ref:`Notes ` + .. |SPECIES| replace:: :ref:`SPECIES ` .. |SPECIES.Species| replace:: :ref:`Species ` diff --git a/doc/src/housekeeping.m4 b/doc/src/housekeeping.m4 index 6e04cd7..94bf888 100644 --- a/doc/src/housekeeping.m4 +++ b/doc/src/housekeeping.m4 @@ -39,5 +39,6 @@ The tables in this section are all in the ``housekeeping`` schema. housekeeping/food_variations.rst housekeeping/community_membership_update_log.rst housekeeping/pantgrunt_event_log.rst + housekeeping/siv_status_bout.rst housekeeping/subadult_arrivals_log.rst housekeeping/tlk_brecord_notes_codes.rst diff --git a/doc/src/housekeeping/siv_status_bout.m4 b/doc/src/housekeeping/siv_status_bout.m4 new file mode 100644 index 0000000..3c77e1d --- /dev/null +++ b/doc/src/housekeeping/siv_status_bout.m4 @@ -0,0 +1,215 @@ +.. Copyright (C) 2026 The Meme Factory, Inc. www.karlpinc.com + + This program is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License as + published by the Free Software Foundation, either version 3 of the + License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with this program. If not, see . + +.. M4 setup +include(constants.m4)dnl +include(macros.m4)dnl +sdb_rst_quotes(`on')dnl +sdb_generated_rst()dnl + +.. _SIV_STATUS_BOUT: + +SIV_STATUS_BOUT +--------------- + +.. |SIV_STATUS_BOUT_summary| replace:: + A data frame with 181 rows and 10 columns. + This table has data on windows of infections status among Gombe + chimps. + +|SIV_STATUS_BOUT_summary| + +Note that this table only contains KNOWN NEGATIVE and KNOWN POSITIVE +periods, as well as periods of UNKNOWN status for individuals that +seroconverted ONLY. +Other periods not covered in this table have an unknown status. + +Yingying confirmed that individuals are never expected to go from +positive to negative, so for those individuals in the biography table, +negative status extends from birth to date of last negative sample, +while positive periods go from date of first positive sample (when ID +is red in the original tables) until departure date in the biography +table. +For individuals not present in the biography table, start and end +dates are the dates of first and last sample of the given infection +status. + +.. contents:: + :depth: 2 + + +.. _SIV_STATUS_BOUT.ID: + +ID (IDentifier) +``````````````` + +.. |SIV_STATUS_BOUT.ID_summary| replace:: |idcol| + +|SIV_STATUS_BOUT.ID_summary| |notnull| + + +.. _SIV_STATUS_BOUT.AnimID: + +AnimID +`````` + +.. |SIV_STATUS_BOUT.AnimID_summary| replace:: + Either a |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| or some other + individual id. + +|SIV_STATUS_BOUT.AnimID_summary| +This column is *not* validated against |BIOGRAPHY_DATA|.\ +|BIOGRAPHY_DATA.AnimID|. + +|notnull| + + +.. _SIV_STATUS_BOUT.StartDate: + +StartDate +````````` + +.. |SIV_STATUS_BOUT.StartDate_summary| replace:: + Start date of a given status, not necessarily date of first sample + with a given status. + +|SIV_STATUS_BOUT.StartDate_summary| + +See above for more details. +|notnull| + + +.. _SIV_STATUS_BOUT.EndDate: + +EndDate +``````` + +.. |SIV_STATUS_BOUT.EndDate_summary| replace:: + End date of a given status, not necessarily date of last sample + with a given status. + +|SIV_STATUS_BOUT.EndDate_summary| + +See above for more details. +|notnull| + + +.. _SIV_STATUS_BOUT.SIVStatus: + +SIVStatus +````````` + +.. |SIV_STATUS_BOUT.SIVStatus_summary| replace:: + The SIV status of the individual. + +|SIV_STATUS_BOUT.SIVStatus_summary| +The values in this column are not validated. + +``neg`` + negative + +``pos`` + positive + +``unk`` + unknown + +|notnull| + + +.. _SIV_STATUS_BOUT.FirstTestDate: + +FirstTestDate +````````````` + +.. |SIV_STATUS_BOUT.FirstTestDate_summary| replace:: + Date of first sample of given status. + +|SIV_STATUS_BOUT.FirstTestDate_summary| +See above for more details. + +|notnull| + + +.. _SIV_STATUS_BOUT.LastTestDate: + +LastTestDate +```````````` + +.. |SIV_STATUS_BOUT.LastTestDate_summary| replace:: + Date of last sample of given status. + +|SIV_STATUS_BOUT.LastTestDate_summary| +See above for more details. + +|notnull| + + +.. _SIV_STATUS_BOUT.BoutNumber: + +BoutNumber +`````````` + +.. |SIV_STATUS_BOUT.BoutNumber_summary| replace:: + Bout of unique status periods for given individual, i.e. second/ + third bouts are unknown and positive periods for individuals that + seroconvert. + +|SIV_STATUS_BOUT.BoutNumber_summary| + +|notnull| + + +.. _SIV_STATUS_BOUT.NamesUsed: + +NamesUsed +````````` + +.. |SIV_STATUS_BOUT.NamesUsed_summary| replace:: + Names associated with a given ID in the raw files from Yingying. + + +|SIV_STATUS_BOUT.NamesUsed_summary| + +|notnull| + + +.. _SIV_STATUS_BOUT.InBioTable: + +InBioTable +`````````` + +.. |SIV_STATUS_BOUT.InBioTable_summary| replace:: + A |boolean| value, |true| when the ID is found in the + |BIOGRAPHY_DATA| table. + +|SIV_STATUS_BOUT.InBioTable_summary| +The value of this column is not validated. + +|notnull| + + +.. _SIV_STATUS_BOUT.Notes: + +Notes +````` + +.. |SIV_STATUS_BOUT.Notes_summary| replace:: + Additional notes on how data were compiled for the table. + +|SIV_STATUS_BOUT.Notes_summary| + +|notonlyspaces| +|notnull| -- 2.34.1